package mak.twitterSentiment.classes;
import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServlet;
import javax.sql.DataSource;

//Class for the connection to the database
public class ConnectionToDB extends HttpServlet{
	private DataSource ds;
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	
	//Connect to database
	public void connect(){
		try{
			 Context initContext  = new InitialContext();
	         Context envContext  = (Context)initContext.lookup("java:/comp/env");
	         ds = (DataSource)envContext.lookup("jdbc/twitter");
	 
			connect = ds.getConnection();
		}catch(Exception e){
			throw new Error(e);
		}
	}
	
	//Insert a stem with a part of speech and a polarity to database
	public void insertToDB(String stem, String pos, String polarity){
		try{
			statement = connect.createStatement();
			String query = "insert into  twitter.stem_pos (stem, part_of_speech, polarity) values (?, ?, ?)";
			preparedStatement = connect.prepareStatement(query);
			preparedStatement.setString(1, stem);
			preparedStatement.setString(2, pos);
			preparedStatement.setString(3, polarity);
		    preparedStatement.execute();
		}
		catch(Exception e){
			
		}
	}
	
	//Search for a stem with a specific part of speech in the database
	public String executeQuery(String stem, String category, String word){
		try{
		  statement = connect.createStatement();
	      // Search the table with stems, parts of speech and polarities
		  String query1 = "SELECT * FROM twitter.stem_pos WHERE stem=?";
		  preparedStatement = connect.prepareStatement(query1);
		  preparedStatement.setString(1, stem);
	      resultSet = preparedStatement.executeQuery();
	      String result = "";
	      while (resultSet.next()) {
		      String pos = resultSet.getString("part_of_speech");
		      String polarity = resultSet.getString("polarity");
		      if(pos.equals(category)){
		    	  result = polarity;
		      }
		   }
	      
	      
	      return result;
		}catch(Exception e){
			throw new Error(e);
		}
	      
	}
	
	public Connection getConnection(){
		return connect;
	}
	
	//Close connection to database
	public static boolean close(Connection c){
		try{
			c.close();
			return true;
		}catch(Exception e){
			return false;
		}
	}
}
